package com.library.dao;

import com.library.entity.Borrowrec;
import com.library.util.C3p0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BorrowrecDao {
    private final QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());

    // 查询学生所有未归还借阅记录
    public List<Object[]> getUnreturnedBySno(String sno) {
        String sql = "SELECT br.serNum, br.barCode, bk.bname, br.borDate, br.retDate, DATEDIFF(CURDATE(), br.retDate) AS ovdDays " +
                "FROM borrowrec br JOIN bookcopy bc ON br.barCode = bc.barCode JOIN book bk ON bc.ISBN = bk.ISBN " +
                "WHERE br.sno = ? AND br.retStatus = '未归还'";
        try {
            List<Object[]> result = new ArrayList<>();
            List<Object[]> raw = queryRunner.query(sql, new ArrayListHandler(), sno);
            if (raw != null) result.addAll(raw);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new ArrayList<>();
    }
    // 插入借阅记录
    public void insertBorrowrec(String sno, String barCode) {
        String sql = "INSERT INTO borrowrec (sno, barCode, borDate, retDate, retStatus) VALUES (?, ?, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 30 DAY), '未归还')";
        try {
            queryRunner.update(sql, sno, barCode);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    // 查询个人借书记录，可按状态筛选
    public List<Object[]> getBorrowRecords(String sno, String status) {
        StringBuilder sql = new StringBuilder(
                "SELECT br.serNum, br.barCode, bk.bname, br.borDate, br.retDate, br.realRetDate, br.retStatus, br.fineMoney, br.fineStatus " +
                        "FROM borrowrec br " +
                        "JOIN bookcopy bc ON br.barCode = bc.barCode " +
                        "JOIN book bk ON bc.ISBN = bk.ISBN " +
                        "WHERE br.sno = ?"
        );
        List<Object> params = new ArrayList<>();
        params.add(sno);
        if (status != null && !status.isEmpty() && !"全部".equals(status)) {
            sql.append(" AND br.retStatus = ?");
            params.add(status);
        }
        sql.append(" ORDER BY br.borDate DESC");
        try {
            List<Object[]> result = new ArrayList<>();
            List<Object[]> raw = queryRunner.query(sql.toString(), new ArrayListHandler(), params.toArray());
            if (raw != null) result.addAll(raw);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new ArrayList<>();
    }
    // 查询个人罚款情况，可按状态筛选
    public List<Object[]> getFineList(String sno, String fineStatus) {
        StringBuilder sql = new StringBuilder(
                "SELECT br.serNum, br.barCode, bk.bname, br.borDate, br.realRetDate, br.fineMoney, br.fineStatus, pr.payDate " +
                        "FROM borrowrec br " +
                        "JOIN bookcopy bc ON br.barCode = bc.barCode " +
                        "JOIN book bk ON bc.ISBN = bk.ISBN " +
                        "LEFT JOIN payrec pr ON br.paySerNum = pr.serNum " +
                        "WHERE br.sno = ? AND br.fineMoney > 0"
        );
        List<Object> params = new ArrayList<>();
        params.add(sno);
        if (fineStatus != null && !fineStatus.isEmpty() && !"全部".equals(fineStatus)) {
            sql.append(" AND br.fineStatus = ?");
            params.add(fineStatus);
        }
        sql.append(" ORDER BY br.borDate DESC");
        try {
            List<Object[]> result = new ArrayList<>();
            List<Object[]> raw = queryRunner.query(sql.toString(), new ArrayListHandler(), params.toArray());
            if (raw != null) result.addAll(raw);
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new ArrayList<>();
    }
    // 还书，超期罚款10元
    public String returnBook(String serNum) {
        String selectSql = "SELECT barCode, retDate, retStatus FROM borrowrec WHERE serNum = ?";
        String updateBorrowSql = "UPDATE borrowrec SET realRetDate = CURDATE(), retStatus = ?, ovdDays = ?, fineMoney = ?, fineStatus = ? WHERE serNum = ?";
        String updateCopySql = "UPDATE bookcopy SET status = 1 WHERE barCode = ?";
        try (var conn = C3p0Utils.getDataSource().getConnection()) {
            conn.setAutoCommit(false);
            QueryRunner runner = new QueryRunner();
            // 1. 查询借阅记录
            Borrowrec rec = runner.query(conn, selectSql, new BeanHandler<>(Borrowrec.class), serNum);
            if (rec == null) {
                conn.rollback();
                return "未找到该借阅记录！";
            }
            String barCode = rec.getBarCode();
            java.sql.Date retDate = rec.getRetDate();
            String retStatus = rec.getRetStatus();
            if (!"未归还".equals(retStatus)) {
                conn.rollback();
                return "该书已归还！";
            }
            // 2. 判断是否超期
            int ovdDays = 0;
            double fine = 0;
            java.sql.Date today = new java.sql.Date(System.currentTimeMillis());
            if (retDate != null && today.after(retDate)) {
                ovdDays = (int) ((today.getTime() - retDate.getTime()) / (1000 * 60 * 60 * 24));
                fine = 10;
            }
            // 3. 更新借阅记录
            runner.update(conn, updateBorrowSql,
                    fine > 0 ? "超时还" : "按时还",
                    ovdDays,
                    fine,
                    fine > 0 ? "未缴" : "已缴",
                    serNum);
            // 4. 更新副本状态
            runner.update(conn, updateCopySql, barCode);
            conn.commit();
            return fine > 0 ? "归还成功，但超期需罚款10元，请尽快缴纳罚款！" : "归还成功！";
        } catch (Exception e) {
            e.printStackTrace();
            return "归还失败，系统异常！";
        }
    }
    // 交罚款
    public String payFine(String serNum, String sno, double fine) {
        String insertPaySql = "INSERT INTO payrec(sno, payAmount, payDate) VALUES (?, ?, CURDATE())";
        String updateBorrowSql = "UPDATE borrowrec SET fineStatus = '已缴', paySerNum = ? WHERE serNum = ?";
        try (var conn = C3p0Utils.getDataSource().getConnection()) {
            conn.setAutoCommit(false);
            QueryRunner runner = new QueryRunner();
            // 1. 插入付款记录
            runner.update(conn, insertPaySql, sno, fine);
            // 2. 获取新插入的付款流水号
            Object paySerNumObj = runner.query(conn, "SELECT LAST_INSERT_ID()", new org.apache.commons.dbutils.handlers.ScalarHandler<>());
            int paySerNum = 0;
            if (paySerNumObj instanceof Integer) {
                paySerNum = (Integer) paySerNumObj;
            } else if (paySerNumObj instanceof Long) {
                paySerNum = ((Long) paySerNumObj).intValue();
            } else if (paySerNumObj instanceof java.math.BigInteger) {
                paySerNum = ((java.math.BigInteger) paySerNumObj).intValue();
            }
            // 3. 更新借阅记录
            runner.update(conn, updateBorrowSql, paySerNum, serNum);
            conn.commit();
            return "交款成功！";
        } catch (Exception e) {
            e.printStackTrace();
            return "交款失败，系统异常！";
        }
    }
    /**
     * 统计书籍副本借阅记录
     * @return Map<barCode, 借阅次数>
     */
    public java.util.Map<String, Integer> countBookCopyRecs() {
        String sql = "select bc.barCode, count(bc.barCode) as copyCount from borrowrec br left join bookcopy bc on br.barCode = bc.barCode group by bc.barCode order by count(bc.barCode)";
        try {
            return queryRunner.query(sql, new org.apache.commons.dbutils.ResultSetHandler<java.util.Map<String, Integer>>() {
                @Override
                public java.util.Map<String, Integer> handle(java.sql.ResultSet rs) throws java.sql.SQLException {
                    java.util.Map<String, Integer> result = new java.util.HashMap<>();
                    while (rs.next()) {
                        result.put(rs.getString("barCode"), rs.getInt("copyCount"));
                    }
                    return result;
                }
            });
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    /**
     * 统计学生借阅记录
     * @return Map<sno, 借阅次数>
     */
    public java.util.Map<String, Integer> countStudentRecs() {
        String sql = "select sno, count(sno) as borrowCount from borrowrec group by sno";
        try {
            return queryRunner.query(sql, new org.apache.commons.dbutils.ResultSetHandler<java.util.Map<String, Integer>>() {
                @Override
                public java.util.Map<String, Integer> handle(java.sql.ResultSet rs) throws java.sql.SQLException {
                    java.util.Map<String, Integer> result = new java.util.HashMap<>();
                    while (rs.next()) {
                        result.put(rs.getString("sno"), rs.getInt("borrowCount"));
                    }
                    return result;
                }
            });
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
} 